package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.dto.account.group.AccountGroupDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.myspringbootcore.common.constant.ROLE_ACCOUNT_GROUP_ADMIN
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional

@Service
class AccountGroupService(
    private val dslContext: DSLContext,
    private val fileService: FileService,
    private val incomeExpenseDetailTypeService: IncomeExpenseDetailTypeService,
    private val roleService: RoleService,
    private val userService: UserService,
    private val userActiveTokenService: UserActiveTokenService,
) {
    /**
     * Queries user's account group info by user account group id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The map instance which contains keys below.
     * id: String of the user account group id.
     * title: String of the title.
     */
    fun queryAccountGroupInfo(accountGroupId: String, userId: String): Map<String, Any?> {
        val owner = dslContext
            .select(
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
            )
            .from(ACCOUNT_GROUP_USER)
            .join(USER).on(
                ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID).
                and(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(USER.ACCOUNT_GROUP_ID))
            )
            .where(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
            )
            .fetchOneMap()

        return if (owner != null) mapOf(
            "id" to accountGroupId,
            "isOwner" to (owner["id"] == userId),
            "ownerId" to owner["id"],
            "ownerUsername" to owner["username"],
            "ownerFullName" to owner["fullName"]
        ) else emptyMap()
    }

    /**
     * Creates a new AccountGroup and a new User.
     * This typically used by new user register.
     * During register the username and password are only needed.
     * The other details can be added later.
     *
     * @param username: The username used for login.
     * @param password: The password used for login.
     * @param fullName: The full name used for display.
     */
    @Transactional
    fun register(username: String, password: String, fullName: String) {
        // Gets the ACCOUNT_GROUP_ADMIN role id.
        val roleId = roleService.queryId(ROLE_ACCOUNT_GROUP_ADMIN)

        // Creates a new AccountGroup which will be used by the new User.
        val accountGroup = dslContext.newRecord(ACCOUNT_GROUP).apply {
            title = "我的账户"
            insert()  // You can call insert inside the apply block
        }

        // Prepares the default income/expense detail type for the created account group.
        incomeExpenseDetailTypeService.init(accountGroup.id)

        // Creates a new User with the created AccountGroup and username, password.
        val user = dslContext.newRecord(USER).apply {
            accountGroupId = accountGroup.id
            this.username = username
            this.password = password
            this.fullName = fullName
            insert()
        }

        // Defines relationship between account group and user.
        dslContext.newRecord(ACCOUNT_GROUP_USER).run {
            accountGroupId = accountGroup.id
            userId = user.id
            isAccountGroupCreator = true
            insert()
        }

        // Assigns the default role to the created user.
        dslContext.newRecord(USER_ROLE).run {
            accountGroupId = accountGroup.id
            userId = user.id
            this.roleId = roleId
            insert()
        }
    }

    /**
     * IMPORTANT!!!
     * THIS ACTION CANNOT BE UNDONE.
     * Clears own account group, including users and all data and quits from all other account groups.
     *
     * @param userId: String of user id.
     */
    @Transactional
    fun unregister(userId: String) {
        // Queries user's avatar filename.
        val avatarFilename = dslContext.selectFrom(USER)
            .where(USER.ID.eq(userId))
            .fetchOne(USER.AVATAR_FILENAME)

        // Queries user's own account group id.
        val accountGroupId = dslContext.selectFrom(ACCOUNT_GROUP_USER)
            .where(
                ACCOUNT_GROUP_USER.USER_ID.eq(userId)
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
            )
            .fetchOne(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID)

        if (!accountGroupId.isNullOrBlank()) {
            // All users with this account group as their current account group will be restored to their own.
            userService.restoreAccountGroupByAccountGroupId(accountGroupId)

            // Deletes active tokens which currently logged in to this account group to force re-login.
            userActiveTokenService.deleteByAccountGroup(accountGroupId)
        }

        // Removes current user from all other account groups.
        // Removes all users from this account group.
        dslContext.deleteFrom(ACCOUNT_GROUP_USER)
            .where(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .or(ACCOUNT_GROUP_USER.USER_ID.eq(userId))
            )
            .execute()

        // Clears all the data of this account group.
        dslContext.deleteFrom(JOURNEY_INCOME_EXPENSE_DETAIL)
            .where(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .execute()

        dslContext.deleteFrom(JOURNEY)
            .where(JOURNEY.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .execute()

        dslContext.deleteFrom(INCOME_EXPENSE_DETAIL)
            .where(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .execute()

        dslContext.deleteFrom(INCOME_EXPENSE_DETAIL_TYPE)
            .where(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .execute()

        dslContext.deleteFrom(BANK_CARD)
            .where(BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .execute()

        dslContext.deleteFrom(USER_ROLE)
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .or(USER_ROLE.USER_ID.eq(userId))
            )
            .execute()

        // Deletes all its pending requests.
        dslContext.deleteFrom(JOIN_ACCOUNT_GROUP_REQUEST)
            .where(
                JOIN_ACCOUNT_GROUP_REQUEST.SENDER_ID.eq(userId)
                    .or(JOIN_ACCOUNT_GROUP_REQUEST.RECEIVER_ID.eq(userId))
                    .or(JOIN_ACCOUNT_GROUP_REQUEST.OWNER_ID.eq(userId))
            )
            .execute()

        // Deletes current user.
        dslContext.deleteFrom(USER)
            .where(USER.ID.eq(userId))
            .execute()

        // Deletes this account group.
        dslContext.deleteFrom(ACCOUNT_GROUP)
            .where(ACCOUNT_GROUP.ID.eq(accountGroupId))
            .execute()

        // Deletes avatar file if exists.
        if (!avatarFilename.isNullOrBlank()) {
            fileService.delete(avatarFilename)
        }
    }

    fun update(@Valid data: AccountGroupDto) {
        dslContext.newRecord(ACCOUNT_GROUP).run {
            from(data)
            changed(ACCOUNT_GROUP.ID, false)
            update()
        }
    }
}